/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.querie.collab;

import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;


import com.querie.db.DBMSConnection;
import com.querie.util.QryParameters;


/**
 *
 * @author a-skoshy
 */
public class CosineItemSimilarity extends ItemSimilarity{

    

    public CosineItemSimilarity() throws SQLException {
        super();

    }

   @Override

    /* Backend process for finding the item to item similarity for all items in the system.
     * Calls the stored procedure and writes results to ItemSimilarity table
     */
    public void calculateAllItemSimilarity(boolean isCategorzied) throws SQLException {
       Connection csCon = DBMSConnection.getConnection();
       CallableStatement cStmt = null;
        String simiSQL = null;
        try {
            if (isCategorzied) {
            simiSQL = "{call CalcSepItemCosineSimis()}";
            }else{
            simiSQL = "{call CalcCosineSimilarities()}";
            }
            System.out.println(simiSQL);
            cStmt = csCon.prepareCall(simiSQL);
            cStmt.setQueryTimeout(1800000);
            cStmt.execute();
        } finally {
            DBMSConnection.safeClose(null, cStmt, csCon);
        }
    }

  

   

    /*
     *
     * Find all items in the current session
     * Find prediction for each item
     * Find the highest n prediction

     *
     */
    public String findTopPredictedItems(int sessionID) throws Exception {
        System.out.println("findTopPredictedItems " + sessionID);
        QryParameters qParam = QryParameters.getInstance();
        String prediction = qParam.getValue("PREDICTION");
        String topN = qParam.getValue("TOPN");
        String colWt = qParam.getValue("COLW");
        String tableWt = qParam.getValue("TABLEW");
        String whereWt = qParam.getValue("WHEREW");
        String includeAll = "";
        String itemid = null;
        float predVal = 0.0f;

        if(prediction.equals("excl"))
            includeAll = " and b.itemid is null ";


 String sql = "select \n" +
"    itemid, pred \n" +
" from  \n"+
"    ( \n"+
"    select  \n"+
 "       itemid, (case when(dene = 0) then 0 else nume/dene end) pred   \n"+
  "  from   \n"+
   "     ( \n"+
    "    select   \n"+
     "       simi.itemid,  sum(simivalue) dene,   \n" +
      "      sum(simivalue * (case when (us.itemid is null) then 0 else us.rating end)* " +
      "     (case when ifnull(istable,'N') = 'Y' then " + tableWt + " else " +
      "     (case when ifnull(iswhere,'N') = 'Y' then " + whereWt + " else " +
      "     (case when ifnull(iscolumn,'N') = 'Y' then " + colWt + " else 1 end) end) end)) nume   \n"+
       " from   \n"+

         "   ( \n"+
"            select   \n"+
 "               distinct a.itemid,t.istable,t.iscolumn,t.iswhere,t.item2, t.simivalue    \n"+
  "          from  \n" +
   "             collabitems a left outer join  \n"+
    "            (   \n"+
     "           select  \n" +
      "              q.itemid   \n"+
       "         from qryitems q, sessionqueries s   \n"+
        "        where q.queryid= s.queryid and sessionid= ? \n"+
         "       ) b  \n"+
          "          on a.itemid=b.itemid   \n"+
           "         join (select item1, item2, simivalue,c.istable,c.iscolumn,c.iswhere from topkitemsimilarity tk, collabitems c \n" +
            "        where tk.item2 = c.itemid) t on a.itemid=t.item1  where istraining = 'Y' \n"+
          // "         where b.itemid is null) simi  left outer join   \n"+
           includeAll + "   ) simi  left outer join   \n"+
            "        (select  \n"+
             "           itemid , count(*) rating \n"+
              "      from qryitems q, sessionqueries s   \n"+
               "     where q.queryid= s.queryid and sessionid= ? \n"+
 "                   group by itemid)us  \n" +
  "                  on simi.item2=us.itemid  group by simi.itemid) predTable   \n"+
   "             order by pred desc  limit " +  topN + " )tab where pred > 0 \n";

        
        System.out.println(sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
        String listStr = "";

        pstmt.setInt(1, sessionID);
        pstmt.setInt(2, sessionID);
        rs = pstmt.executeQuery();
        listStr = "";
        while (rs.next()) {
            itemid = rs.getString(1);
            predVal = rs.getFloat(2);
            System.out.println("Item ID:" + itemid + " " + "Predicted Value:" + predVal);
            
            listStr = listStr + rs.getString(1) + ",";
        }
        if (!listStr.equals("")) {
            listStr = listStr.substring(0, listStr.length() - 1);
        }
        return listStr;
    }

  

  
}
